split partition用法

**************************

当删除分区表分区的时候，drop 和truncate都会导致global索引失效，需要注意。 

可以加参数 update global indexes  在11g的时候，这个参数会影响数据库性能，所以我们一般都是之后手动rebuild索引。 

但是在12c及以后，这个参数更加智能，会把工作安排到数据库空闲状态进行，但是我们一般还是选择把删除分区这种操作在空闲时候执行，然后手动重建索引。

对于本地索引，删除分区表的时候不会影响到本地索引的状态。

**************************

当分区表有最大分区的时候，不能直接添加分区，就需要用到split partition：

下面是示例：

create table a (id number, name varchar2(30),name1 varchar2(30),name2 varchar2(30),time date) partition by range (time)( partition p2013 values less than (to_date('2014-01-01', 'yyyy-mm-dd')), partition p2014 values less than (to_date('2015-01-01', 'yyyy-mm-dd')), partition p2015 values less than (to_date('2016-01-01', 'yyyy-mm-dd')), partition p2016 values less than (to_date('2017-01-01', 'yyyy-mm-dd')), partition p2017 values less than (to_date('2018-01-01', 'yyyy-mm-dd')), partition p2018 values less than (to_date('2019-01-01', 'yyyy-mm-dd')), partition p2019 values less than (to_date('2020-01-01', 'yyyy-mm-dd')), partition pmax values less than (maxvalue) );

select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';SQL> select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';

TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------TEST1 A P2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A PMAX MAXVALUE

8 rows selected.

下面我们加分区：SQL> alter table test1.a add partition "p2020" values less than (to_date('2021-01-01','yyyy-mm-dd')) tablespace "TEST1";alter table test1.a add partition "p2020" values less than (to_date('2021-01-01','yyyy-mm-dd')) tablespace "TEST1" *ERROR at line 1:ORA-14074: partition bound must collate higher than that of the last partition

alter table test1.a add partition "P2012" values less than (to_date('2013-01-01','yyyy-mm-dd')) tablespace "TEST1";

SQL> alter table test1.a add partition "P2012" values less than (to_date('2013-01-01','yyyy-mm-dd')) tablespace "TEST1";alter table test1.a add partition "P2012" values less than (to_date('2013-01-01','yyyy-mm-dd')) tablespace "TEST1" *ERROR at line 1:ORA-14074: partition bound must collate higher than that of the last partition

因为已经有MAXVALUE这个值，这个时候不允许直接增加分区，而是通过拆分分区实现的。

alter table test1.a add partition "p2020" values less than (to_date('2021-01-01','yyyy-mm-dd')) tablespace "TEST1";

增加分区p2020

alter table a split partition pmax at(to_date('2021-01-01','yyyy-mm-dd')) INTO (PARTITION P2020,partition pmax);SQL> select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';

TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------TEST1 A P2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2020 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A PMAX MAXVALUE

9 rows selected.

增加分区p2012alter table a split partition p2013 at (to_date('2013-01-01','yyyy-mm-dd'))INTO (PARTITION p2012,partition p2013);SQL> select TABLE_OWNER,table_name,partition_name,high_value from dba_tab_partitions where table_name='A' and TABLE_OWNER='TEST1';

TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------TEST1 A P2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2018 TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2019 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A P2020 TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIATEST1 A PMAX MAXVALUE

10 rows selected.

 自己总结语法：

alter table table_owner.table_name splite partition_name at (partition_values) into (PARTITION partiition_name TABLESPACE tablespace_name NOCOMPRESS,PARTITION partition_new_name TABLESPACE tablespace_name NOCOMPRESS) parallel 16;

 解释：

partition_name是已经存在的分区。 partition_new_name 是我们要生成的分区。

 可以指定表空间和是否压缩。  NOCOMPRESS

可以并行。